package com.vasthu.core.dao;

import com.vasthu.api.dao.UserDAO;
import com.vasthu.api.domain.User;
import com.vasthu.utility.VstDBManager;
import java.util.*;
import java.sql.*;
import java.util.logging.Level;
import org.apache.log4j.Logger;

public class UserDAOImpl implements UserDAO {

    static Logger logger = Logger.getLogger(UserDAOImpl.class);

    public boolean insertUser(User user) {
        if (user == null) {
            return false;
        }
        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("INSERT INTO User(user_username, user_password, user_name, "
                    + "user_address, user_city, user_nic, user_phone, user_email, user_role_id, "
                    + "user_salary, user_dob, status) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1)");

            pst.setString(1, user.getUserUserName());
            pst.setString(2, user.getUserPassword());
            pst.setString(3, user.getUserName());
            pst.setString(4, user.getUserAddress());
            pst.setString(5, user.getUserCity());
            pst.setString(6, user.getUserNIC());
            pst.setString(7, user.getUserPhone());
            pst.setString(8, user.getUserEmail());
            pst.setLong(9, user.getUserRoleId());
            pst.setFloat(10, user.getUserSalary());
            pst.setDate(11, new java.sql.Date(user.getUserDob().getTime()));
            pst.executeUpdate();
        } catch (Exception ex) {
            logger.error("unable to insert user : " + ex);
        } finally {
            VstDBManager.get().close();
        }
        return true;
    }

    public boolean updateUser(User user) {
        if (user == null) {
            return false;
        }
        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("UPDATE User SET user_username=" + user.getUserUserName()
                    + ", user_password = " + user.getUserPassword() + ", user_name = " + user.getUserName()
                    + ", user_address = " + user.getUserAddress() + ", user_city = " + user.getUserCity()
                    + ", user_nic = " + user.getUserNIC() + ", user_phone = " + user.getUserPhone()
                    + ", user_email = " + user.getUserEmail() + ", user_role_id = " + user.getUserRoleId()
                    + ", user_salary = " + user.getUserSalary() + ", user_dob = " + new java.sql.Date(user.getUserDob().getTime())
                    + ", status = 1) WHERE (user_id = " + user.getUserId() + ")");

            pst.executeUpdate();

        } catch (Exception ex) {
            logger.error("unable to update user : " + ex);
        } finally {
            VstDBManager.get().close();
        }
        return true;
    }

    public User getUserById(long userId) {

        return new User();
    }

    public boolean deleteUserById(long userId) {
        PreparedStatement pst = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("DELETE FROM User WHERE user_id = ?");
            pst.setLong(1, userId);
            pst.executeUpdate();
        } catch (Exception ex) {
            logger.debug("unable to delete user by id = " + userId + " : " + ex);
        } finally {
            VstDBManager.get().close();
        }
        return true;
    }

    public List<User> getAllUsers() {
        List<User> userList = new ArrayList<User>();
        PreparedStatement pst = null;
        ResultSet rs = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM User WHERE status = 1");
            rs = pst.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUserName(rs.getString("user_name"));
                user.setUserPhone(rs.getString("user_phone"));
                user.setUserEmail(rs.getString("user_email"));
                user.setUserAddress(rs.getString("user_address"));
                user.setUserCity(rs.getString("user_city"));
                user.setUserUserName(rs.getString("user_username"));
                user.setUserPassword(rs.getString("user_password"));
                user.setUserNIC(rs.getString("user_nic"));
                user.setUserRoleId(rs.getInt("user_role_id"));
                user.setUserSalary(rs.getFloat("user_salary"));
                user.setUserDob(rs.getDate("user_dob"));
                userList.add(user);
            }
        } catch (SQLException ex) {
            logger.debug("unable to get users " + ex);
        } finally {
            VstDBManager.get().close();
        }
        return userList;
    }

    @Override
    public User getUserByUserName(String uName) {
        PreparedStatement pst = null;
        User user = null;
        ResultSet rs = null;
        Connection con = VstDBManager.get().getConnection();
        try {
            pst = con.prepareStatement("SELECT * FROM User WHERE user_username = ?");
            pst.setString(1, uName);
            rs = pst.executeQuery();

            while (rs.next()) {
                user = new User();
                user.setUserId(rs.getInt("user_id"));
                user.setUserName(rs.getString("user_name"));
                user.setUserPhone(rs.getString("user_phone"));
                user.setUserEmail(rs.getString("user_email"));
                user.setUserAddress(rs.getString("user_address"));
                user.setUserCity(rs.getString("user_city"));
                user.setUserUserName(rs.getString("user_username"));
                user.setUserPassword(rs.getString("user_password"));
                user.setUserNIC(rs.getString("user_nic"));
                user.setUserRoleId(rs.getInt("user_role_id"));
                user.setUserSalary(rs.getFloat("user_salary"));
                user.setUserDob(rs.getDate("user_dob"));
            }
        } catch (SQLException ex) {
            logger.error("unable to select user for given username = " + uName + " : " + ex);
        } finally {
            VstDBManager.get().close();
        }
        return user;
    }
}
